﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;//用来连接数据库的
using System.Data;//这是用来使用这个的： //关闭连接对象
//return comm.ExecuteReader(CommandBehavior.CloseConnection);    

namespace StuManagementSystem
{
    /// <summary>
    /// 数据库操作类 
    /// </summary>
    public class DbContext
    {
        /// <summary>
        /// 链接对象
        /// </summary>
        SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=StuManagementSystem;Uid=sa;Pwd=123");

        #region ExecuteNonQuery
        /// <summary>
        /// 执行新增、删除、修改
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public bool ExecNonQuery(string sql)
        {
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                MessageBox.Show("异常!错误信息是：" + ex.Message + "请联系工作人员排除问题！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                System.Environment.Exit(0);
                return false;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
        
        #region ExecuteScalar
        /// <summary>
        /// 执行查询，返回单个值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public object ExecScaler(string sql)
        {
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                return comm.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show("异常!错误信息是：" + ex.Message + "请联系工作人员排除问题！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                System.Environment.Exit(0);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// 执行多个查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回数据读取器</returns>
        public SqlDataReader ExecReader(string sql)
        {
            
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                //CommandBehavior.CloseConnection表示关闭读取器的时候，自动
                //关闭连接对象
                return comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                MessageBox.Show("异常!错误信息是：" + ex.Message + "请联系工作人员排除问题！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                System.Environment.Exit(0);
                return null;
            }
        }
        #endregion

        #region 数据适配器
        /// <summary>
        /// 数据适配器
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet ExecDataSet(string sql)
        {
            try
            {
                //数据适配器
                var adp = new SqlDataAdapter(sql, conn);
                //数据集
                DataSet ds = new DataSet();
                //填充数据集
                adp.Fill(ds);
                return ds;
            }
            catch(Exception ex)
            {
                MessageBox.Show("异常!错误信息是：" + ex.Message+"请联系工作人员排除问题！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                System.Environment.Exit(0);
                return null;
            }
        }
        #endregion
    }
}
